Consultas complejas y vistas

Introducción

En este tema aprenderemos a trabajar con subconsultas anidadas, consultas complejas y vistas.

Subconsultas anidadas

Una subconsulta anidada se define como una sentencia de tipo SELECT-FROM-WHERE que está dentro de otra consulta. Normalmente, las consultas anidadas se utilizan para comprobar pertenencia a conjuntos, así como para cardinalidades y comparar conjuntos.

Para ejemplificar los diferentes puntos de este tema, vamos a utilizar las siguientes relaciones, que modelan la información de venta y presupuestos de una empresa.

xxxxxxxxxx
6
1
sucursal (nombre_sucursal, ciudad_sucursal, activos)
2
cliente (nombre_cliente, calle_cliente, ciudad_cliente)
3
prestamo (numero_prestamo, nombre_sucursal, importe)
4
prestatario (nombre_cliente, numero_prestamo)
5
cuenta (numero_cuenta, nombre_sucursal, saldo)
6
impositor (nombre_cliente, numero_cuenta)

Subconsultas anidadas independiendes

La subconsulta y la consulta no “están ligadas”, son independientes.

Se puede considerar que se ejecutan por separado, generando la tabla resultado de combinar ambas consultas según las condiciones contenidas en la consulta.

Ejemplo: Determinar los clientes que tienen tanto un préstamo como una cuenta en la sucursal de Navacerrada:

xxxxxxxxxx
8
1
SELECT distinct nombre_cliente
2
FROM prestatario, prestamo
3
WHERE prestatario.numero_prestamo = prestamo.numero_prestamo and
4
      nombre_sucursal = "Navacerrada" and
5
      (nombres_sucursal, nombre_cliente) in (
6
        SELECT nombre_sucursal, nombre_cliente
7
        FROM impositor, cuenta
8
        WHERE impositor.numero_cuenta = cuenta.numero_cuenta);

Ejemplo: Obtener el identificador de los clientes que han comprado algún coche a un concesionario de Madrid:

xxxxxxxxxx
6
1
SELECT DISTINCT idCli
2
FROM venta
3
WHERE idCli IN (
4
  SELECT idCli
5
  FROM concesionario
6
  WHERE ciudad = 'Madrid');

Subconsultas anidadas dependientes

Las condiciones de selección de la consulta y la subconsulta “están entrelazadas”.

Se puede considerar que para cada valor de interés para la consulta se ejecuta la subconsulta para determinar si, según la condición impuesta, se selecciona o no el elemento.

Ejemplo: Clientes que tienen tanto cuenta como préstamo en la entidad bancaria:

xxxxxxxxxx
5
1
SELECT nombre_cliente
2
FROM prestatario
3
WHERE exists (SELECT *
4
              FROM impositor
5
              WHERE impositor.nombre_cliente = prestatario.nombre_cliente);

Pertenencia a conjuntos

La cláusula in permite comprobar si un conjunto de tuplas pertenece a un conjunto. Equivalentemente, la cláusula not in permite comprobar que las tuplas no pertenecen a un conjunto. La ejecución se realiza comprobando que las tuplas del SELECT más externo están (o no) en el resultado de la subconsulta interna.

Encontrar todos los clientes que tienen tanto un préstamo como una cuenta en el banco:

xxxxxxxxxx
4
1
SELECT distinct nombre_cliente
2
FROM prestatario
3
WHERE nombre_cliente in (SELECT nombre_cliente
4
FROM impositor);

También podemos hacer comprobaciones con relaciones que tienen más de un atributo.

Encontrar todos los clientes que tienen un préstamo en el banco, pero no tienen una cuenta:

xxxxxxxxxx
4
1
SELECT distinct nombre_cliente
2
FROM prestatario
3
WHERE nombre_cliente not in (SELECT nombre_cliente
4
FROM impositor);

Además, los operadores in y not in se pueden utilizar sobre conjuntos enumerados.

Clientes que tienen un préstamo que no son “Santos” ni “Gómez”:

xxxxxxxxxx
3
1
SELECT distinct nombre_cliente
2
FROM prestatario
3
WHERE nombre_sucursal not in ("Santos","Gómez");

Comparación de conjuntos

Un tipo de consulta bastante habitual es encontrar valores «mayores que, al menos, un…». Para este tipo de consultas, SQL permite utilizar las partículas some y any.

Estas partículas se utilizan para indicar una condición en la cláusula WHERE y podemos establecer las comparaciones habituales: <, >, <=, >=, <>.

Obtener las sucursales que tienen activos mayores que, al menos, una sucursal de Arganzuela:

xxxxxxxxxx
5
1
SELECT nombre_sucursal
2
FROM sucursal
3
WHERE activos > any (SELECT activos
4
                     FROM sucursal
5
                     WHERE ciudad_sucursal = "Arganzuela");

Lo que hace la subconsulta es seleccionar los activos de todas las sucursales de Arganzuela, constituyendo el conjunto con el que se va a comparar.

Es importante darse cuenta de que la subconsulta devuelve únicamente los activos.

La consulta externa selecciona todas las sucursales y, para cada una de ellas, compara el valor de sus activos con el conjunto generado en la subconsulta. De esta forma, si el valor de la tupla que se está comparando es mayor que alguno de los valores del conjunto, se incorpora al resultado.

El atributo de comparación que indicamos en la cláusula WHERE y el tipo del conjunto que se devuelve en la subconsulta deben ser compatibles.

Otra consulta habitual es querer recuperar los valores «mayores que todos…». Para este tipo de consultas, podemos utilizar all, cuyo funcionamiento es similar al de las partículas some y any, salvo que hace referencia a todos.

Encontrar el nombre de todas las sucursales que tienen activos superiores al de todas las sucursales de Arganzuela:

xxxxxxxxxx
5
1
SELECT nombre_sucursal
2
FROM sucursal
3
WHERE activos > all (SELECT activos
4
                     FROM sucursal
5
                     WHERE ciudad_sucursal = "Arganzuela");

Finalmente, otro uso habitual de las subconsultas es resolver cuestiones que, a priori, podrían resolverse anidando funciones de agregación. Sin embargo, las funciones de agregación no se pueden componer. Por ese motivo, una forma de resolver este tipo de cuestiones es utilizando subconsultas.

Encontrar el nombre de todas las sucursales, agrupadas por nombre de sucursal, que tienen saldos medios superiores o iguales al saldo medio de todas las sucursales:

xxxxxxxxxx
6
1
SELECT nombre_sucursal
2
FROM cuenta
3
group by nombre_sucursal
4
having avg(saldo) >= all (SELECT avg(saldo)
5
                          FROM cuenta
6
                          group by nombre_sucursal);

El resultado de la subconsulta es el saldo medio de todas las sucursales, que constituye el conjunto con el que haremos la comparación.

La consulta externa vuelve a consultar sobre los saldos medios agrupados por sucursal, pero ahora se queda con el atributo nombre_sucursal, que es lo que queremos devolver a nuestro usuario.

Comprobación de relaciones vacías (EXISTS)

En SQL podemos comprobar si las subconsultas tienen alguna tupla en su resultado, para ello utilizamos el predicado exists, que devuelve true si la subconsulta no resulta vacía. De forma equivalente, podemos utilizar not exists para comprobar que el resultado de la subconsulta no devuelve ninguna tupla.

Listar todos los clientes que tienen tanto una cuenta abierta como un préstamo concedido en el banco:

xxxxxxxxxx
5
1
SELECT nombre_cliente
2
FROM prestatario
3
WHERE exists (SELECT nombre_cliente
4
              FROM impositor
5
              WHERE impositor.nombre_cliente = prestatario.nombre_cliente);

Comprobación de la ausencia de tuplas duplicadas

En SQL se puede comprobar si las subconsultas tienen tuplas duplicadas en su resultado. El constructor unique devuelve el valor true si la subconsulta que se le pasa como argumento no contiene tuplas duplicadas.

En mariadb unique se expresa como 1 = count()*.

Obtener todos los clientes que tienen, a lo sumo, una cuenta en la sucursal de Navacerrada:

xxxxxxxxxx
7
1
SELECT nombre_cliente
2
FROM impositor as T
3
WHERE unique (SELECT nombre_cliente
4
              FROM cuenta, impositor as R
5
              WHERE T.nombre_cliente = R.nombre_cliente and
6
                    R.numero_cuenta = cuenta.numero_cuenta and
7
                    cuenta.nombre_sucursal = "Navacerrada");

En mariadb la consulta se realizaria:

xxxxxxxxxx
7
1
SELECT nombre_cliente
2
FROM impositor as T
3
WHERE 1 = (SELECT count(*)
4
           FROM cuenta, impositor as R
5
           WHERE T.nombre_cliente = R.nombre_cliente and
6
                R.numero_cuenta = cuenta.numero_cuenta and
7
                cuenta.nombre_sucursal = "Navacerrada");

Consultas complejas

Hay consultas que no podemos resolver utilizando la unión, intersección o diferencia, o con subconsultas como las que hemos aprendido en el apartado anterior. Para este tipo de consultas, vamos a aprender distintas maneras de componer varios bloques de SQL.

Relaciones derivadas

Estas se obtienen utilizando subconsultas en la cláusula FROM, de tal forma que componemos una nueva relación sobre la que hacemos la selección y aplicamos las condiciones del WHERE. Para poder utilizarlas, las subconsultas deberán ir entre paréntesis y es necesario renombrar esa relación que se genera mediante la cláusula as.

En lugar de utilizar una tabla existente para seleccionar filas o columnas, la tabla sobre la que se realiza la consulta se crea en el aire.

Saldo total máximo de las sucursales:

xxxxxxxxxx
5
1
SELECT max(saldo_total)
2
FROM (
3
  SELECT nombre_sucursal, sum(saldo) as saldo_total
4
  FROM cuenta
5
  GROUP BY nombre_sucursal) as total_sucursal;

La cláusula with

Las consultas complicadas son más fáciles de formular si se descomponen en fragmentos más pequeños que después podemos combinar.

Estos fragmentos serán vistas temporales que desaparecen cuando finaliza la consulta en la que las definimos. En SQL, ese comportamiento lo conseguimos con la cláusula with.

Sintaxis:

• La sentencia se inicia con la palabra reservada with.

• Sigue una lista de consultas y nombres separados por comas.

• Se puede nombrar tanto la tabla como los atributos.

Determinar las sucursales donde el depósito total de las cuentas es mayor que la media de los depósitos totales de las cuentas de todas las sucursales:

xxxxxxxxxx
9
1
with total_sucursales (nombre_sucursal, valor) as (
2
  SELECT nombre_sucursal, sum(saldo)
3
  FROM cuenta
4
  GROUP BY nombre_sucursal), media_total_sucursales(valor) as (
5
    SELECT avg (valor)
6
    FROM total_sucursales)
7
SELECT nombre_sucursal
8
FROM total_sucursales, media_total_sucursales
9
WHERE total_sucursales.valor >= media_total_sucursales.valor;

Vistas

Habitualmente, diferentes departamentos de una empresa necesitan trabajar con datos distintos. Además, es posible que algunos de esos datos con los que no trabajan sean sensibles y no puedan estar accesibles para todos los usuarios del sistema. Con las vistas conseguimos limitar o adaptar los datos a los que cada usuario tiene acceso.

Las vistas se actualizan “solas” porque sólo son “ventanas” a las tablas que las sustentan.

Para ejemplificar este apartado, vamos a utilizar la siguiente tabla:

Definición de vistas

Para definir vistas utilizamos la instrucción CREATE VIEW.

Crear una vista de los estudiantes aprobados:

xxxxxxxxxx
4
1
CREATE VIEW alumnos_aprobados as(
2
  SELECT name, score
3
  FROM student
4
  WHERE score >= 5);